package edu.student.models;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import com.util.DB;
import edu.student.models.*;
/**
Author: Alpna
**/
public class Enrollment {
	private String rollNo;
	private int subjectId;
	private String subjectCode;
	public String getSubjectName() {
		return subjectName;
	}
	public void setSubjectName(String subjectName) {
		this.subjectName = subjectName;
	}
	public String getSemesterName() {
		return semesterName;
	}
	public void setSemesterName(String semesterName) {
		this.semesterName = semesterName;
	}
	private String grade;
	private String gradePt;
	private String result;
	private String subjectName;
	private String semesterName;
	private int semesterId;
	private String isEnrolled;
	/*//private ArrayList<String> semesterList = new ArrayList<String>();
	//private ArrayList<String> subjectList = new ArrayList<String>();
	public  ArrayList<String> getSubjectList() {
		return subjectList;
	}

	public  void setSubjectList(ArrayList<String> subjectList) {
		this.subjectList = subjectList;
	}

	public ArrayList<String> getSemesterList() {
		return semesterList;
	}*/
	
	public String getRollNo() {
		return rollNo;
	}
	public void setRollNo(String rollNo) {
		this.rollNo = rollNo;
	}
	public int getSubjectId() {
		return subjectId;
	}
	public void setSubjectId(int subjectId) {
		this.subjectId = subjectId;
	}
	public String getGrade() {
		return grade;
	}
	public void setGrade(String grade) {
		this.grade = grade;
	}
	public String getResult() {
		return result;
	}
	public void setResult(String result) {
		this.result = result;
	}
	public int getSemesterId() {
		return semesterId;
	}
	public void setSemesterId(int semesterId) {
		this.semesterId = semesterId;
	}
	public String getIsEnrolled() {
		return isEnrolled;
	}
	public void setIsEnrolled(String isEnrolled) {
		this.isEnrolled = isEnrolled;
	}
	
	public String getGradePt() {
		return gradePt;
	}
	public void setGradePt(String gradePt) {
		this.gradePt = gradePt;
	}
	
	public String getSubjectCode() {
		return subjectCode;
	}
	public void setSubjectCode(String subjectCode) {
		this.subjectCode = subjectCode;
	}
	
	/*code for updating students enrollment details*/
	public static int insertintoenrollment(String rollNo,int subjectId,int semesterId){
		int recnum = 0;
		String insertQuery = "insert into enrollment (roll_no,subject_id,semester_id,is_enrolled)values(?,?,?,?)";
		try{
			Connection con = DB.getConnection();
			try{
				PreparedStatement st = con.prepareStatement(insertQuery);
				st.setString(1,rollNo);
				st.setInt(2,subjectId);
				st.setInt(3,semesterId);
				st.setString(4,"Y");
				recnum = st.executeUpdate();
			}
			finally{
				con.close();
			}
		}
		catch(Exception e){
			System.out.println(e);
		}
		
			
		return recnum;
	}
	/*code for fetching semester id for a particular user*/
	public static ArrayList<String> getSemesterListForUser(String rollNoVal) {
		ArrayList<String> semesters = new ArrayList<String>();
		ResultSet resultSet = null;
		String query = "select distinct(semester_id) from enrollment where roll_No="+"'"+rollNoVal+"'" ;
		System.out.println("\nINSIDE getSemesterListForUser() of Enrollment.java class....\n");
		System.out.println("QUERY FOR SEMESTER ID IS "+query);
		Connection connection = DB.getConnection();
		resultSet = DB.readFromDB(query, connection);
		try {
			while (resultSet.next()) {
				int semesterIdTemp =resultSet.getInt("semester_id");
				/*fetch the value of semester name for the id*/
				String semesterNameTemp =Semester.getSemesterName(semesterIdTemp);
				semesters.add(semesterNameTemp);
			}
		} catch (SQLException e) {
	       System.out.println("Exception while reading from db"+ e);
		}
		DB.close(connection);
		return semesters;
	}
	/*code for fetching subject id for a particular semester*/
	public static ArrayList<String> getSubjectListForSemester(String rollNoVal,int semesterId) {
		ArrayList<String> subjects = new ArrayList<String>();
		ResultSet resultSet = null;
		String query = "select subject_id from enrollment where roll_no="+"'"+rollNoVal+"'"+" and semester_id=" +semesterId+" " ;
		System.out.println("\nINSIDE getSubjectListForSemester() of Enrollment.java class....\n");
		System.out.println("query in enroll is"+query);
		Connection connection = DB.getConnection();
		resultSet = DB.readFromDB(query, connection);
		try {
			while (resultSet.next()) {
				int subjectIdTemp =resultSet.getInt("subject_id");
				/*fetch the value of semester name for the id*/
				String subjectNameTemp =Subject.getSubjectName(subjectIdTemp);
				subjects.add(subjectNameTemp);
			}
		} catch (SQLException e) {
	       System.out.println("Exception while reading from db"+ e);
		}
		DB.close(connection);
		return subjects;
	}
	
	/*code for fetching semester ids */
	public static ArrayList<String> getSemesterList() {
		ArrayList<String> semesters = new ArrayList<String>();
		ResultSet resultSet = null;
		String query = "select distinct(semester_id) from enrollment ";
		System.out.println("\nINSIDE getSemesterListForUser() of Enrollment.java class....\n");
		System.out.println("QUERY FOR SEMESTER ID IS "+query);
		Connection connection = DB.getConnection();
		resultSet = DB.readFromDB(query, connection);
		try {
			while (resultSet.next()) {
				int semesterIdTemp =resultSet.getInt("semester_id");
				/*fetch the value of semester name for the id*/
				String semesterNameTemp =Semester.getSemesterName(semesterIdTemp);
				semesters.add(semesterNameTemp);
			}
		} catch (SQLException e) {
	       System.out.println("Exception while reading from db"+ e);
		}
		DB.close(connection);
		return semesters;
	}
	/*code for fetching distinct subject id for a particular semester*/
	public static ArrayList<String> getSubjectList() {
		ArrayList<String> subjects = new ArrayList<String>();
		ResultSet resultSet = null;
		String grade="";
		String query = "select distinct(subject_id) from enrollment " ;
		System.out.println("\nINSIDE getSubjectList of Enrollment.java class....\n");
		System.out.println("query in enroll is"+query);
		Connection connection = DB.getConnection();
		resultSet = DB.readFromDB(query, connection);
		try {
			while (resultSet.next()) {
				int subjectIdTemp =resultSet.getInt("subject_id");
				/*fetch the value of semester name for the id*/
				String subjectNameTemp =Subject.getSubjectName(subjectIdTemp);
				subjects.add(subjectNameTemp);
			}
		} catch (SQLException e) {
	       System.out.println("Exception while reading from db"+ e);
		}
		DB.close(connection);
		return subjects;
	}
	
	public static int updateResultInEnroll(String rollNo,int semesterId,int subjectId,String grade,String result,String gradePt){
		int rs = 0;
		String query = "update Enrollment set grade=?,result=?,grade_point=? where roll_No=? and semester_id=? and subject_id=? and is_enrolled=?";
		Float f = Float.parseFloat(gradePt);
		try{
			Connection connection = DB.getConnection();
			try{
				String isEnrolled="y";
				PreparedStatement ps = connection.prepareStatement(query);
				ps.setString(1,grade);
				ps.setString(2,result);
				ps.setFloat(3, f);
				ps.setString(4,rollNo);
				ps.setInt(5,semesterId);
				ps.setInt(6,subjectId);
				ps.setString(7,isEnrolled);
				System.out.println("query is"+query);
				rs = ps.executeUpdate();
			}finally{
				DB.close(connection);
				}
		}
		catch (SQLException e) {
            System.out.println("Exception while reading from db"+ e);
        }
		
		return rs;
	}
	public static ArrayList<Enrollment> getRecords(String selectionModifier) {
		ArrayList<Enrollment> selection = new ArrayList<Enrollment>();
		ResultSet rs = null;

		String query = "select * from Enrollment where is_enrolled='y'"+selectionModifier;
		System.out.println("query is"+query);
		try{
			Connection connection = DB.getConnection();
			try{
				PreparedStatement ps = connection.prepareStatement(query);
				rs = ps.executeQuery();
				while (rs.next()) {
					Enrollment enroll = new Enrollment();
					String rollNoTemp = rs.getString("roll_no");
					 int semesterIdTemp= rs.getInt("semester_id");
					 int subjectIdTemp= rs.getInt("subject_id");
					String gradeTemp = rs.getString("grade");
					String resultTemp = rs.getString("result");
					Float f = rs.getFloat("grade_point");
					enroll.gradePt = Float.toString(f);
					enroll.rollNo=rollNoTemp;
					enroll.semesterId=semesterIdTemp;
					enroll.subjectId=subjectIdTemp;
					/*get the subject name and semester name*/
					enroll.subjectName=Subject.getSubjectName(subjectIdTemp);
					enroll.semesterName = Semester.getSemesterName(semesterIdTemp);
					enroll.grade=gradeTemp;
					enroll.result=resultTemp;
					selection.add(enroll);
				}
			}finally{
				connection.close();
			}
	}
	catch (SQLException e) {
        e.printStackTrace();
    }
		return selection;
	}
	
	/*check details */
	public static int checkDetails(String rollNo, int semesterId, int subjectId){
		ResultSet rs = null;

		String query = "select grade from Enrollment where roll_no=? and semester_id=? and subject_id=? and is_enrolled=?";
		System.out.println("query is"+query);
		int count=0;
		try{
			Connection connection = DB.getConnection();
			try{
				PreparedStatement ps = connection.prepareStatement(query);
				ps.setString(1,rollNo);
				ps.setInt(2,semesterId);
				ps.setInt(3,subjectId);
				ps.setString(4,"Y");
				rs = ps.executeQuery();
				while (rs.next()) {
					count=count+1;
				}
			}finally{
				connection.close();
			}
	}
	catch (SQLException e) {
        e.printStackTrace();
    }
		return count;
	}
	
}
